package com.fintech.third.utils.shzx;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;

import com.fintech.third.constant.Constants;
import com.fintech.third.model.ThirdProperties;
import com.fintech.third.model.shzx.ExcelInfo;
import com.fintech.third.model.shzx.IdentityInformation;
import com.fintech.third.model.shzx.InvestorInformation;
import com.fintech.third.model.shzx.LoanApplicationInformation;
import com.fintech.third.model.shzx.LoanBusinessInformation;
import com.fintech.third.model.shzx.LoanContractInformation;
import com.fintech.third.model.shzx.PersonalDataDeletionInformation;
import com.fintech.third.model.shzx.ProfessionalInformation;
import com.fintech.third.model.shzx.ResidentialInformation;
import com.fintech.third.model.shzx.SpecialTransactionInformation;
import com.fintech.third.model.shzx.WarrantyInformation;
import com.fintech.third.utils.ThirdPropertiesInitUtil;
import com.marvels.common.utils.MarvelsLogUtil;

import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class WriteDataUtil {

	public static String writeExcel(ExcelInfo excelInfo) {
		ThirdProperties thirdProperties = ThirdPropertiesInitUtil.getThirdProperties();
		String realsavepath = WriteDataUtil.class.getResource(thirdProperties.getImage_path()).getPath();
		WritableWorkbook wwb;
		String fileStr = "";
		String filePath = "";
		String[] titleSheet0 = { "P2P机构代码", "姓名", "证件类型", "证件号码", "性别", "出生日期", "婚姻状况", "最高学历", "最高学位", "住宅电话", "手机号码",
				"单位电话", "电子邮箱", "通讯地址", "通讯地址邮政", "户籍地址", "配偶姓名", "配偶证件类型", "配偶证件号码", "配偶工作单位", "配偶联系电话", "第一联系人姓名",
				"第一联系人关系", "第一关系人联系电话", "第二联系人姓名", "第二联系人关系", "第二关系人联系电话" };

		String[] titleSheet1 = { "P2P机构代码", "姓名", "证件类型", "证件号码", "职业", "单位名称", "单位所属行业", "单位地址", "单位地址邮政编码",
				"本单位工作起始年份", "职务", "职称", "年收入" };

		String[] titleSheet2 = { "P2P机构代码", "姓名", "证件类型", "证件号码", "居住地址", "居住地址邮政编码", "居住状况" };

		String[] titleSheet3 = { "P2P机构代码", "贷款申请号", "姓名", "证件类型", "证件号码", "贷款申请类型", "贷款申请金额", "贷款申请月数", "贷款申请时间",
				"贷款申请状态" };

		String[] titleSheet4 = { "P2P机构代码", "贷款类型", "贷款合同号码", "业务号", "发生地点", "开户日期", "到期日期", "币种", "授信额度", "共享授信额度",
				"最大负债额", "担保方式", "还款频率", "还款月数", "剩余还款月数", "协定还款期数", "协定期还款额", "结算/应还款日期", "最近一次实际还款日期", "本月应还款金额",
				"本月实际还款金额", "余额", "当前逾期期数", "当前逾期总额", "逾期31-60天未归还贷款本金", "逾期61-90天未归还贷款本金", "逾期91-180天未归还贷款本金",
				"逾期180天以上未归还贷款本金", "累计逾期期数", "最高逾期期数", "五级分类状态", "账户状态", "24月（账户）还款状态", "账户拥有者信息提示", "姓名", "证件类型",
				"证件号码" };

		String[] titleSheet5 = { "P2P机构代码", "贷款合同号码", "贷款合同生效日期", "贷款合同终止日期", "币种", "贷款合同金额", "合同有效状态" };
		String[] titleSheet6 = { "P2P机构代码", "业务号", "担保人姓名", "担保人证件类型", "担保人证件号码", "担保金额", "担保状态" };

		String[] titleSheet7 = { "P2P机构代码", "业务号", "投资人姓名", "投资人证件类型", "投资人证件号码", "投资人投资金额" };

		String[] titleSheet8 = { "P2P机构代码", "姓名", "证件类型", "证件号码", "业务号", "特殊交易类型", "发生日期", "变更月数", "发生金额", "明细信息" };

		// ExcelInfo excelInfo=new ExcelInfo();
		// excelInfo=JSON.parseObject(json.toString(), ExcelInfo.class);
		IdentityInformation identityInformation = null;
		ProfessionalInformation professionalInformation = null;
		ResidentialInformation residentialInformation = null;
		LoanApplicationInformation loanApplicationInformation = null;
		LoanBusinessInformation loanBusinessInformation = null;
		LoanContractInformation loanContractInformation = null;
		WarrantyInformation warrantyInformation = null;
		InvestorInformation investorInformation = null;
		SpecialTransactionInformation specialTransactionInformation = null;
		PersonalDataDeletionInformation personalDataDeletionInformation = null;
		try {
			// 获得开始时间
			long start = System.currentTimeMillis();

			// SimpleDateFormat format=new SimpleDateFormat("yyyyMMddhhmmss");

			// 输出的excel的路径
			// String filePath = "d:/testJXL.xls";

			if (!new File(realsavepath + "temp").exists()) {
				new File(realsavepath + "temp").mkdirs();
			}

			filePath = realsavepath + "temp/" + "excel";
			if (!new File(filePath).exists()) {
				new File(filePath).mkdir();
			}
			fileStr = realsavepath + "temp/" + "excel/" + ZipUtils.getName(Constants.NORMAL) + ".xls";
			// 创建Excel工作薄
			MarvelsLogUtil.getInstance().info("----上海资信Excel name:" + fileStr);
			// 新建立一个jxl文件,即在d盘下生成testJXL.xls
			OutputStream os = new FileOutputStream(fileStr);
			wwb = Workbook.createWorkbook(os);
			Label label0;
			WritableSheet sheet = wwb.createSheet("身份信息", 0);
			for (int i = 0; i < titleSheet0.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label0 = new Label(i, 0, titleSheet0[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label0);
			}
			// 填充数据
			if (null != excelInfo.getIdentityInformation()) {
				for (int i = 0; i < excelInfo.getIdentityInformation().size(); i++) {
					identityInformation = new IdentityInformation();
					int j = i + 1;
					identityInformation = excelInfo.getIdentityInformation().get(i);
					label0 = new Label(0, j, identityInformation.getP2pOrganizationCode());
					sheet.addCell(label0);
					label0 = new Label(1, j, identityInformation.getName());
					sheet.addCell(label0);
					label0 = new Label(2, j, identityInformation.getDocumentType());
					sheet.addCell(label0);
					label0 = new Label(3, j, identityInformation.getIdentificationNumber());
					sheet.addCell(label0);
					label0 = new Label(4, j, identityInformation.getGender());
					sheet.addCell(label0);
					label0 = new Label(5, j, identityInformation.getDateOfBirth());
					sheet.addCell(label0);
					label0 = new Label(6, j, identityInformation.getMaritalStatus());
					sheet.addCell(label0);
					label0 = new Label(7, j, identityInformation.getHighestEducation());
					sheet.addCell(label0);
					label0 = new Label(8, j,identityInformation.getHighestDegree() );
					sheet.addCell(label0);
					label0 = new Label(9, j, identityInformation.getHomePhone());
					sheet.addCell(label0);
					label0 = new Label(10, j, identityInformation.getPhoneNumber());
					sheet.addCell(label0);
					label0 = new Label(11, j, identityInformation.getWorkTelephone());
					sheet.addCell(label0);
					label0 = new Label(12, j, identityInformation.getEmail());
					sheet.addCell(label0);
					label0 = new Label(13, j, identityInformation.getPostalAddress().replaceAll(String.valueOf((char)8205), ""));
					sheet.addCell(label0);
					label0 = new Label(14, j, identityInformation.getPostalCode());
					sheet.addCell(label0);
					label0 = new Label(15, j, identityInformation.getPermanentAddress().replaceAll(String.valueOf((char)8205), ""));
					sheet.addCell(label0);
					label0 = new Label(16, j, identityInformation.getSpouseName());
					sheet.addCell(label0);
					label0 = new Label(17, j, identityInformation.getSpouseCertificateType());
					sheet.addCell(label0);
					label0 = new Label(18, j, identityInformation.getSpouseIDNumber());
					sheet.addCell(label0);
					label0 = new Label(19, j, identityInformation.getSpouseWorkUnit());
					sheet.addCell(label0);
					label0 = new Label(20, j, identityInformation.getSpousePhone());
					sheet.addCell(label0);
					label0 = new Label(21, j, identityInformation.getFirstContactName());
					sheet.addCell(label0);
					label0 = new Label(22, j, identityInformation.getFirstContactRelationship());
					sheet.addCell(label0);
					label0 = new Label(23, j, identityInformation.getFirstContactPersonPhone());
					sheet.addCell(label0);
					label0 = new Label(24, j, identityInformation.getSecondContactName());
					sheet.addCell(label0);
					label0 = new Label(25, j, identityInformation.getSecondContactRelationship());
					sheet.addCell(label0);
					label0 = new Label(26, j, identityInformation.getSecondContactPersonPhone());
					sheet.addCell(label0);
				}
			}
			sheet = wwb.createSheet("职业信息", 1);
			Label label1;
			for (int i = 0; i < titleSheet1.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label1 = new Label(i, 0, titleSheet1[i]);
				// label1 = new Label(i, 0, titleSheet1[i], getHeader());
				// 调整头部的颜色
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label1);
			}

			if (null != excelInfo.getProfessionalInformation()) {
				for (int i = 0; i < excelInfo.getProfessionalInformation().size(); i++) {
					int j = i + 1;
					professionalInformation = new ProfessionalInformation();
					professionalInformation = excelInfo.getProfessionalInformation().get(i);
					label1 = new Label(0, j, professionalInformation.getP2pOrganizationCode());
					sheet.addCell(label1);
					label1 = new Label(1, j, professionalInformation.getName());
					sheet.addCell(label1);
					label1 = new Label(2, j, professionalInformation.getDocumentType());
					sheet.addCell(label1);
					label1 = new Label(3, j, professionalInformation.getIdentificationNumber());
					sheet.addCell(label1);
					label1 = new Label(4, j, professionalInformation.getOccupation());
					sheet.addCell(label1);
					label1 = new Label(5, j, professionalInformation.getUnitName());
					sheet.addCell(label1);
					label1 = new Label(6, j, professionalInformation.getUnitOwnedIndustry());
					sheet.addCell(label1);
					label1 = new Label(7, j, professionalInformation.getUnitAddress().replaceAll(String.valueOf((char)8205), ""));
					sheet.addCell(label1);
					label1 = new Label(8, j, professionalInformation.getUnitAddressZipCode());
					sheet.addCell(label1);
					label1 = new Label(9, j, professionalInformation.getYearOfWorkInThisUnit());
					sheet.addCell(label1);
					label1 = new Label(10, j, professionalInformation.getPost());
					sheet.addCell(label1);
					label1 = new Label(11, j, professionalInformation.getTitle());
					sheet.addCell(label1);
					label1 = new Label(12, j, professionalInformation.getAnnualIncome());
					sheet.addCell(label1);
				}
			}
			// 添加第一个工作表并设置第一个Sheet的名字
			sheet = wwb.createSheet("居住信息", 2);
			Label label2;
			for (int i = 0; i < titleSheet2.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label2 = new Label(i, 0, titleSheet2[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label2);
			}
			// 下面是填充数据

			if (null != excelInfo.getResidentialInformation()) {
				for (int i = 0; i < excelInfo.getResidentialInformation().size(); i++) {
					int j = i + 1;
					residentialInformation = new ResidentialInformation();
					residentialInformation = excelInfo.getResidentialInformation().get(i);
					label2 = new Label(0, j, residentialInformation.getP2pOrganizationCode());
					sheet.addCell(label2);
					label2 = new Label(1, j, residentialInformation.getName());
					sheet.addCell(label2);
					label2 = new Label(2, j, residentialInformation.getDocumentType());
					sheet.addCell(label2);
					label2 = new Label(3, j, residentialInformation.getIdentificationNumber());
					sheet.addCell(label2);
					label2 = new Label(4, j, residentialInformation.getResidentialAddress().replaceAll(String.valueOf((char)8205), ""));
					sheet.addCell(label2);
					label2 = new Label(5, j, residentialInformation.getResidentialAddressPostalCode());
					sheet.addCell(label2);
					label2 = new Label(6, j, residentialInformation.getLivingCondition());
					sheet.addCell(label2);
				}
			}

			/*
			 * // * 保存数字到单元格，需要使用jxl.write.Number 必须使用其完整路径，否则会出现错误 //
			 */
			// // 填充P2P机构代码
			// label2 = new Label(0, 1, "10000000000000");
			// sheet.addCell(label2);
			// // 填充姓名
			// label2 = new Label(1, 1, "测试借款人1");
			// sheet.addCell(label2);
			//
			// // 填充证件类型
			//// jxl.write.Number nb = new jxl.write.Number(2, 1, 0, wcf);
			//// sheet.addCell(nb);
			//
			// label2 = new Label(2, 1, "1");
			// sheet.addCell(label2);
			//
			// // 填充证件号码
			// label2 = new Label(3, 1, "110101198003021234");
			// sheet.addCell(label2);
			//
			// // 填充居住地址
			// label2 = new Label(4, 1, "居住地址1");
			// sheet.addCell(label2);
			//
			// //填充居住地址邮政编码
			// label2 = new Label(5, 1, "100005");
			// sheet.addCell(label2);
			//
			// //填充居住状况
			// label2 = new Label(6, 1, "1");
			// sheet.addCell(label2);
			/*
			 * 定义对于显示金额的公共格式 jxl会自动实现四舍五入 例如 2.456会被格式化为2.46,2.454会被格式化为2.45
			 */
			jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#,###.00");
			jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf);

			// /*
			// * 定义显示日期的公共格式 如:yyyy-MM-dd hh:mm
			// */
			// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			// String newdate = sdf.format(new Date());
			// // 填充出产日期
			// label = new Label(4, 1, newdate);
			// sheet.addCell(label);

			// /*
			// * 显示布尔值
			// */
			// jxl.write.Boolean bool = new jxl.write.Boolean(6, 1, true);
			// sheet.addCell(bool);
			/*
			 * 合并单元格 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的
			 * 表示将从第x+1列，y+1行到m+1列，n+1行合并
			 * 
			 */
			// sheet.mergeCells(0, 3, 2, 3);
			// label2 = new Label(0, 3, "合并了三个单元格");
			// sheet.addCell(label2);
			/*
			 * 
			 * 定义公共字体格式 通过获取一个字体的样式来作为模板 首先通过web.getSheet(0)获得第一个sheet
			 * 然后取得第一个sheet的第二列，第一行也就是"产品名称"的字体
			 */
			// CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();
			// WritableCellFormat wc = new WritableCellFormat();
			// // 设置居中
			// wc.setAlignment(Alignment.CENTRE);
			// // 设置边框线
			// wc.setBorder(Border.ALL, BorderLineStyle.THIN);
			// // 设置单元格的背景颜色
			// wc.setBackground(jxl.format.Colour.RED);
			// label = new Label(1, 5, "字体", wc);
			// sheet.addCell(label);
			//
			// // 设置字体
			// jxl.write.WritableFont wfont = new
			// jxl.write.WritableFont(WritableFont.createFont("隶书"), 20);
			// WritableCellFormat font = new WritableCellFormat(wfont);
			// label = new Label(2, 6, "隶书", font);
			// sheet.addCell(label);

			sheet = wwb.createSheet("贷款申请信息", 3);
			Label label3;
			for (int i = 0; i < titleSheet3.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label3 = new Label(i, 0, titleSheet3[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label3);
			}
			if (null != excelInfo.getLoanApplicationInformation()) {
				for (int i = 0; i < excelInfo.getLoanApplicationInformation().size(); i++) {
					int j = i + 1;
					loanApplicationInformation = new LoanApplicationInformation();
					loanApplicationInformation = excelInfo.getLoanApplicationInformation().get(i);
					label3 = new Label(0, j, loanApplicationInformation.getP2pOrganizationCode());
					sheet.addCell(label3);
					label3 = new Label(1, j, loanApplicationInformation.getLoanApplicationNumber());
					sheet.addCell(label3);
					label2 = new Label(2, j, loanApplicationInformation.getName());
					sheet.addCell(label2);
					label3 = new Label(3, j, loanApplicationInformation.getDocumentType());
					sheet.addCell(label3);
					label3 = new Label(4, j, loanApplicationInformation.getIdentificationNumber());
					sheet.addCell(label3);
					label3 = new Label(5, j, loanApplicationInformation.getTypeOfLoanApplication());
					sheet.addCell(label3);
					label3 = new Label(6, j, loanApplicationInformation.getLoanApplicationAmount());
					sheet.addCell(label3);
					label3 = new Label(7, j, loanApplicationInformation.getMonthsOfLoanApplications());
					sheet.addCell(label3);
					label3 = new Label(8, j, loanApplicationInformation.getLoanApplicationTime());
					sheet.addCell(label3);
					label3 = new Label(9, j, loanApplicationInformation.getLoanApplicationStatus());
					sheet.addCell(label3);
				}
			}

			sheet = wwb.createSheet("贷款业务信息", 4);
			Label label4;
			for (int i = 0; i < titleSheet4.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label4 = new Label(i, 0, titleSheet4[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label4);
			}

			if (null != excelInfo.getLoanBusinessInformation()) {
				for (int i = 0; i < excelInfo.getLoanBusinessInformation().size(); i++) {
					int j = i + 1;
					loanBusinessInformation = new LoanBusinessInformation();
					loanBusinessInformation = excelInfo.getLoanBusinessInformation().get(i);
					label4 = new Label(0, j, loanBusinessInformation.getP2pOrganizationCode());
					sheet.addCell(label4);
					label4 = new Label(1, j, loanBusinessInformation.getLoanType());
					sheet.addCell(label4);
					label4 = new Label(2, j, loanBusinessInformation.getLoanContractNumber());
					sheet.addCell(label4);
					label4 = new Label(3, j, loanBusinessInformation.getServiceNumber());
					sheet.addCell(label4);
					label4 = new Label(4, j, loanBusinessInformation.getPlaceOfOccurrence());
					sheet.addCell(label4);
					label4 = new Label(5, j, loanBusinessInformation.getOpeningDate());
					sheet.addCell(label4);
					label4 = new Label(6, j, loanBusinessInformation.getMaturityDate());
					sheet.addCell(label4);
					label4 = new Label(7, j, loanBusinessInformation.getCurrency());
					sheet.addCell(label4);
					label4 = new Label(8, j, loanBusinessInformation.getCreditLine());
					sheet.addCell(label4);
					label4 = new Label(9, j, loanBusinessInformation.getSharedCreditLine());
					sheet.addCell(label4);
					label4 = new Label(10, j, loanBusinessInformation.getMaximumLiabilities());
					sheet.addCell(label4);
					label4 = new Label(11, j, loanBusinessInformation.getGuaranteeMode());
					sheet.addCell(label4);
					label4 = new Label(12, j, loanBusinessInformation.getRepaymentFrequency());
					sheet.addCell(label4);
					label4 = new Label(13, j, loanBusinessInformation.getMonthsOfRepayment());
					sheet.addCell(label4);
					label4 = new Label(14, j, loanBusinessInformation.getRemainingMonths());
					sheet.addCell(label4);
					label4 = new Label(15, j, loanBusinessInformation.getAgreedRepaymentPeriod());
					sheet.addCell(label4);
					label4 = new Label(16, j, loanBusinessInformation.getAgreedAmount());
					sheet.addCell(label4);
					label4 = new Label(17, j, loanBusinessInformation.getSettlementDueDate());
					sheet.addCell(label4);
					label4 = new Label(18, j, loanBusinessInformation.getTheLastActualRepaymentDate());
					sheet.addCell(label4);
					label4 = new Label(19, j, loanBusinessInformation.getRepaymentAmountThisMonth());
					sheet.addCell(label4);
					label4 = new Label(20, j, loanBusinessInformation.getActualRepaymentAmountThisMonth());
					sheet.addCell(label4);
					label4 = new Label(21, j, loanBusinessInformation.getBalance());
					sheet.addCell(label4);
					label4 = new Label(22, j, loanBusinessInformation.getCurrentOverdue());
					sheet.addCell(label4);
					label4 = new Label(23, j, loanBusinessInformation.getCurrentTotalOverdue());
					sheet.addCell(label4);
					label4 = new Label(24, j, loanBusinessInformation.getOverdue31To60DaysPrincipal());
					sheet.addCell(label4);
					label4 = new Label(25, j, loanBusinessInformation.getOverdue61To90DaysPrincipal());
					sheet.addCell(label4);
					label4 = new Label(26, j, loanBusinessInformation.getOverdue91To180DaysPrincipal());
					sheet.addCell(label4);
					label4 = new Label(27, j, loanBusinessInformation.getOverdue180DaysPrincipal());
					sheet.addCell(label4);
					label4 = new Label(28, j, loanBusinessInformation.getCumulativeOverdue());
					sheet.addCell(label4);
					label4 = new Label(29, j, loanBusinessInformation.getMaximumOverdue());
					sheet.addCell(label4);
					label4 = new Label(30, j, loanBusinessInformation.getFiveLevelClassification());
					sheet.addCell(label4);
					label4 = new Label(31, j, loanBusinessInformation.getAccountStatus());
					sheet.addCell(label4);
					label4 = new Label(32, j, loanBusinessInformation.getRepaymentStatus());
					sheet.addCell(label4);
					label4 = new Label(33, j, loanBusinessInformation.getAccountOwnerInformation());
					sheet.addCell(label4);
					label4 = new Label(34, j, loanBusinessInformation.getName());
					sheet.addCell(label4);
					label4 = new Label(35, j, loanBusinessInformation.getDocumentType());
					sheet.addCell(label4);
					label4 = new Label(36, j, loanBusinessInformation.getIdentificationNumber());
					sheet.addCell(label4);
				}

			}
			sheet = wwb.createSheet("贷款合同信息", 5);
			Label label5;
			for (int i = 0; i < titleSheet5.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label5 = new Label(i, 0, titleSheet5[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label5);
			}
			if (null != excelInfo.getLoanContractInformation()) {
				for (int i = 0; i < excelInfo.getLoanContractInformation().size(); i++) {
					int j = i + 1;
					loanContractInformation = new LoanContractInformation();
					loanContractInformation = excelInfo.getLoanContractInformation().get(i);
					label5 = new Label(0, j, loanContractInformation.getP2pOrganizationCode());
					sheet.addCell(label5);
					label5 = new Label(1, j, loanContractInformation.getLoanContractNumber());
					sheet.addCell(label5);
					label5 = new Label(2, j, loanContractInformation.getEffectiveDateOfLoanContract());
					sheet.addCell(label5);
					label5 = new Label(3, j, loanContractInformation.getLoanContractTerminationDate());
					sheet.addCell(label5);
					label5 = new Label(4, j, loanContractInformation.getCurrency());
					sheet.addCell(label5);
					label5 = new Label(5, j, loanContractInformation.getLoanContractAmount());
					sheet.addCell(label5);
					label5 = new Label(6, j, loanContractInformation.getContractStatus());
					sheet.addCell(label5);
				}
			}

			sheet = wwb.createSheet("担保信息", 6);
			Label label6;
			for (int i = 0; i < titleSheet6.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label6 = new Label(i, 0, titleSheet6[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label6);
			}
			if (null != excelInfo.getWarrantyInformation()) {

				for (int i = 0; i < excelInfo.getWarrantyInformation().size(); i++) {
					int j = i + 1;
					warrantyInformation = new WarrantyInformation();
					warrantyInformation = excelInfo.getWarrantyInformation().get(i);
					label6 = new Label(0, j, warrantyInformation.getP2pOrganizationCode());
					sheet.addCell(label6);
					label6 = new Label(1, j, warrantyInformation.getServiceNumber());
					sheet.addCell(label6);
					label6 = new Label(2, j, warrantyInformation.getGuarantorName());
					sheet.addCell(label6);
					label6 = new Label(3, j, warrantyInformation.getGuarantorCertificateType());
					sheet.addCell(label6);
					label6 = new Label(4, j, warrantyInformation.getGuarantorCertificateNumber());
					sheet.addCell(label6);
					label6 = new Label(5, j, warrantyInformation.getGuaranteeAmount());
					sheet.addCell(label6);
					label6 = new Label(6, j, warrantyInformation.getSecurityStatus());
					sheet.addCell(label6);
				}
			}

			sheet = wwb.createSheet("投资人信息", 7);
			Label label7;
			for (int i = 0; i < titleSheet7.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label7 = new Label(i, 0, titleSheet7[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label7);
			}
			if (null != excelInfo.getInvestorInformation()) {

				for (int i = 0; i < excelInfo.getInvestorInformation().size(); i++) {
					int j = i + 1;
					investorInformation = new InvestorInformation();
					investorInformation = excelInfo.getInvestorInformation().get(i);
					label7 = new Label(0, j, investorInformation.getP2pOrganizationCode());
					sheet.addCell(label7);
					label7 = new Label(1, j, investorInformation.getServiceNumber());
					sheet.addCell(label7);
					label7 = new Label(2, j, investorInformation.getInvestorName());
					sheet.addCell(label7);
					label7 = new Label(3, j, investorInformation.getInvestorCertificateType());
					sheet.addCell(label7);
					label7 = new Label(4, j, investorInformation.getInvestorCertificateNumber());
					sheet.addCell(label7);
					label7 = new Label(5, j, investorInformation.getInvestmentAmount());
					sheet.addCell(label7);

				}

			}
			sheet = wwb.createSheet("特殊交易信息", 8);
			Label label8;
			for (int i = 0; i < titleSheet8.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label8 = new Label(i, 0, titleSheet8[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label8);
			}

			if (null != excelInfo.getSpecialTransactionInformation()) {

				for (int i = 0; i < excelInfo.getSpecialTransactionInformation().size(); i++) {
					int j = i + 1;
					specialTransactionInformation = new SpecialTransactionInformation();
					specialTransactionInformation = excelInfo.getSpecialTransactionInformation().get(i);
					label8 = new Label(0, j, specialTransactionInformation.getP2pOrganizationCode());
					sheet.addCell(label8);
					label8 = new Label(1, j, specialTransactionInformation.getName());
					sheet.addCell(label8);
					label8 = new Label(2, j, specialTransactionInformation.getDocumentType());
					sheet.addCell(label8);
					label8 = new Label(3, j, specialTransactionInformation.getIdentificationNumber());
					sheet.addCell(label8);
					label8 = new Label(4, j, specialTransactionInformation.getServiceNumber());
					sheet.addCell(label8);
					label8 = new Label(5, j, specialTransactionInformation.getSpecialTransactionType());
					sheet.addCell(label8);
					label8 = new Label(6, j, specialTransactionInformation.getOccurrenceDate());
					sheet.addCell(label8);
					label8 = new Label(7, j, specialTransactionInformation.getMonthsOfChange());
					sheet.addCell(label8);
					label8 = new Label(8, j, specialTransactionInformation.getOccurrenceAmount());
					sheet.addCell(label8);
					label8 = new Label(9, j, specialTransactionInformation.getDetailInformation());
					sheet.addCell(label8);
				}

			}
			
			// 写入数据
			wwb.write();
			// 关闭文件
			wwb.close();
			long end = System.currentTimeMillis();
			MarvelsLogUtil.getInstance().info("----完成该操作共用的时间是:" + (end - start) / 1000);
		} catch (Exception e) {
			MarvelsLogUtil.getInstance().error("----生成上海资信Excel异常", e);
			e.printStackTrace();
		}
		return fileStr;
	}

	
	public static String writeDeleteExcel(ExcelInfo excelInfo) {
		ThirdProperties thirdProperties = ThirdPropertiesInitUtil.getThirdProperties();
		String realsavepath = WriteDataUtil.class.getResource(thirdProperties.getImage_path()).getPath();
		WritableWorkbook wwb;
		String fileStr = "";
		String filePath = "";
		
		String[] titleSheet0 = { "P2P机构代码", "业务号", "起始结算/应还款日期", "结束结算/应还款日期" };

		PersonalDataDeletionInformation personalDataDeletionInformation = null;
		
		try {
			// 获得开始时间
			long start = System.currentTimeMillis();

			if (!new File(realsavepath + "temp").exists()) {
				new File(realsavepath + "temp").mkdirs();
			}

			filePath = realsavepath + "temp/" + "excel";
			if (!new File(filePath).exists()) {
				new File(filePath).mkdir();
			}
			fileStr = realsavepath + "temp/" + "excel/" + ZipUtils.getName(Constants.DATAR_EMOVE) + ".xls";
			// 创建Excel工作薄
			MarvelsLogUtil.getInstance().info("----上海资信Excel name:" + fileStr);
			// 新建立一个jxl文件,即在d盘下生成testJXL.xls
			OutputStream os = new FileOutputStream(fileStr);
			wwb = Workbook.createWorkbook(os);
			
			WritableSheet sheet = wwb.createSheet("个人删除数据信息", 0);
			Label label0;
			for (int i = 0; i < titleSheet0.length; i++) {
				// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
				// 在Label对象的子对象中指明单元格的位置和内容
				label0 = new Label(i, 0, titleSheet0[i]);
				// 将定义好的单元格添加到工作表中
				sheet.addCell(label0);
			}
			if (null != excelInfo.getPersonalDataDeletionInformation()) {
				for (int i = 0; i < excelInfo.getPersonalDataDeletionInformation().size(); i++) {
					int j = i + 1;
					personalDataDeletionInformation = new PersonalDataDeletionInformation();
					personalDataDeletionInformation = excelInfo.getPersonalDataDeletionInformation().get(i);
					label0 = new Label(0, j, personalDataDeletionInformation.getP2pOrganizationCode());
					sheet.addCell(label0);
					label0 = new Label(1, j, personalDataDeletionInformation.getServiceNumber());
					sheet.addCell(label0);
					label0 = new Label(2, j, personalDataDeletionInformation.getServiceBeginDate());
					sheet.addCell(label0);
					label0 = new Label(3, j, personalDataDeletionInformation.getServcieEndDate());
					sheet.addCell(label0);
				}
			}
			
			// 写入数据
			wwb.write();
			// 关闭文件
			wwb.close();
			long end = System.currentTimeMillis();
			MarvelsLogUtil.getInstance().info("----删除报文组装完成该操作共用的时间是:" + (end - start) / 1000);
		} catch (Exception e) {
			MarvelsLogUtil.getInstance().error("----生成上海资信删除报文Excel异常", e);
			e.printStackTrace();
		}
		return fileStr;
	}
	
	public static WritableCellFormat getHeader() {
		WritableFont font = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);// 定义字体
		try {
			font.setColour(Colour.BLUE);// 蓝色字体
		} catch (WriteException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
		WritableCellFormat format = new WritableCellFormat(font);
		try {
			format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
			format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			format.setBackground(Colour.YELLOW);// 黄色背景
		} catch (WriteException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		return format;
	}

	/**
	 * 生成txt文件
	 * 
	 * @param str
	 * @return
	 */
	public static String writeTxt(String str) {
		ThirdProperties thirdProperties = ThirdPropertiesInitUtil.getThirdProperties();
		String realsavepath = WriteDataUtil.class.getResource(thirdProperties.getImage_path()).getPath();
		String filePath = "";
		String fileStr = "";
		if (!new File(realsavepath + "temp").exists()) {
			new File(realsavepath + "temp").mkdirs();
		}

		filePath = realsavepath + "temp/" + "txt";
		if (!new File(filePath).exists()) {
			new File(filePath).mkdir();
		}
		fileStr = realsavepath + "temp/" + "txt/" + ZipUtils.getName() + ".txt";
		try {
			FileWriter fwriter = new FileWriter(fileStr);
			fwriter.write(str);
			fwriter.flush();
			fwriter.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return fileStr;
	}

}
